package com.bounds.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.jdbc.core.RowCallbackHandler;

import com.bounds.dao.ItemDao;
import com.bounds.entity.Hospital;
import com.bounds.entity.Item;
import com.bounds.entity.Pager;
import com.bounds.entity.Person;
import com.bounds.entity.Product;

public class ItemDaoImpl extends BaseDaoImpl<Item> implements ItemDao {
	private String ALL_SQL = "SELECT item.*,person.name personName,hospital.name hname,product.name proName FROM item,person,hospital,product " +
			"WHERE item.hospital_id = hospital.id AND	item.person_id=person.id AND item.product_id= product.id order by item.id";
	
	@Override
	public List<Item> qryItems(Item item) {
		StringBuffer sql = new StringBuffer();
		sql.append(ALL_SQL);
		List<Object> params = new ArrayList<Object>();
		if(item != null){
			if(item.getId() != 0){
				sql.append(" AND ID =? ");
				params.add(item.getId());
			}
			if(item.getHospital() != null && item.getHospital().getId() != 0){
				sql.append(" AND hospital_id = ? ");
				params.add(item.getHospital().getId());
			}
			if(item.getPerson() !=null && item.getPerson().getId()!= 0){
				sql.append("AND person_id = ?");
				params.add(item.getPerson().getId());
			}
			if(item.getProduct() !=null && item.getProduct().getId()!= 0){
				sql.append("AND product_id = ?");
				params.add(item.getProduct().getId());
			}
		}
		final List<Item> list = new ArrayList<Item>();
		this.getJdbcTemplate().query(sql.toString(), params.toArray(), new RowCallbackHandler() {		
			@Override
			public void processRow(ResultSet rs) throws SQLException {
				Item item = new Item();
				setItem(rs,item);
				item.getHospital().setName(rs.getString("hname"));
				item.getPerson().setName(rs.getString("personName"));
				item.getProduct().setName(rs.getString("proName"));
				list.add(item);
			}
		});
		return list;
	}

	protected void setItem(ResultSet rs, Item item) throws SQLException {
		item.setBackDate(rs.getDate("backDate"));
		item.setBuyPrice(rs.getDouble("buyPrice"));
		item.setCount(rs.getInt("count"));
		item.setHospital(new Hospital());
		item.getHospital().setId(rs.getInt("hospital_id"));
		item.setId(rs.getInt("id"));
		item.setPerson(new Person());
		item.getPerson().setId(rs.getInt("person_id"));
		item.setProduct(new Product());
		item.getProduct().setId(rs.getInt("product_id"));
		item.setSellDate(rs.getDate("selldate"));
		item.setSellPrice(rs.getDouble("sellPrice"));
		item.setUnit(rs.getString("unit"));
	}

	@Override
	public Pager<Item> qryItemsForPage(Item item,int pageNo,int pageSize) {
		StringBuffer sql = new StringBuffer();
		sql.append(ALL_SQL);
		List<Object> params = new ArrayList<Object>();
		if(item != null){
			if(item.getId() != 0){
				sql.append(" AND ID =? ");
				params.add(item.getId());
			}
			if(item.getHospital() != null && item.getHospital().getId() != 0){
				sql.append(" AND hospital_id = ? ");
				params.add(item.getHospital().getId());
			}
			if(item.getPerson() !=null && item.getPerson().getId()!= 0){
				sql.append("AND person_id = ?");
				params.add(item.getPerson().getId());
			}
			if(item.getProduct() !=null && item.getProduct().getId()!= 0){
				sql.append("AND product_id = ?");
				params.add(item.getProduct().getId());
			}
		}
		this.initPager();
		this.setPageNo(pageNo);
		this.setPageSize(pageSize);	
		int rowCount = this.getRowCount(sql.toString(), params.toArray());
		if(rowCount>0){		
			final List<Item> list = new ArrayList<Item>();
			this.getJdbcTemplate().query(getExecuteSQL(sql.toString()), params.toArray(), new RowCallbackHandler() {		
				@Override
				public void processRow(ResultSet rs) throws SQLException {
					Item item = new Item();
					setItem(rs,item);
					item.getHospital().setName(rs.getString("hname"));
					item.getPerson().setName(rs.getString("personName"));
					item.getProduct().setName(rs.getString("proName"));
					list.add(item);
				}
			});
			this.setData(list);
		}
		return this.getPager();
	}

	@Override
	public Item getItem(Integer id) {
		String sql = ALL_SQL+" AND item.id = "+id;
		final List<Item> list = new ArrayList<Item>();
		this.getJdbcTemplate().query(sql, new RowCallbackHandler() {			
			@Override
			public void processRow(ResultSet rs) throws SQLException {
				Item item = new Item();
				setItem(rs,item);
				item.getHospital().setName(rs.getString("hname"));
				item.getPerson().setName(rs.getString("personName"));
				item.getProduct().setName(rs.getString("proName"));
				list.add(item);
			}
		});
		if(list.isEmpty())
			return null;
		else
			return list.get(0);
	}

	@Override
	public boolean updateItem(Item item) {
		String sql = "UPDATE item SET buyPrice=?,backDate=?,sellDate=?,count=?," +
				"hospital_id=?,person_id=?,product_id=?,sellPrice=?,unit=? WHERE ID =?";
		Object[] object ={
				item.getBuyPrice(),
				item.getBackDate(),
				item.getSellDate(),
				item.getCount(),
				item.getHospital().getId(),
				item.getPerson().getId(),
				item.getProduct().getId(),
				item.getSellPrice(),
				item.getUnit(),
				item.getId()
		};
		return this.getJdbcTemplate().update(sql,object)>0;
	}

	@Override
	public boolean addItem(Item item) {
		String sql = "INSERT item(id,buyPrice,backDate,sellDate,count,hospital_id,person_id,product_id,sellPrice,unit) " +
				"VALUES(?,?,?,?,?,?,?,?,?,?)";
		Object[] object ={
				item.getId(),
				item.getBuyPrice(),
				item.getBackDate(),
				item.getSellDate(),
				item.getCount(),
				item.getHospital().getId(),
				item.getPerson().getId(),
				item.getProduct().getId(),
				item.getSellPrice(),
				item.getUnit()
		};
		return this.getJdbcTemplate().update(sql,object)>0;
	}

	@Override
	public boolean delItem(Integer id) {
		return this.getJdbcTemplate().update("delete from item where id="+id)>0;
	}
	
}
